﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPPEI.Coolzon.Core.Repositories.SQL
{
    public class SqlVersionFileChangesetRepository : IVersionFileChangesetRepository
    {
        public bool AddVersionFileChangeset(DbContext context, Data.VersionFileChangeset versionFileChangeset)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "insert into VersionFileChangeset(Id,Changeset,ChangedUser,ChangedDate,Comment,FileDataId) values(@Id,@Changeset,@ChangedUser,@ChangedDate,@Comment,@FileDataId)";
                    cmd.Parameters.Add(new SqlParameter("Id", versionFileChangeset.Id));
                    cmd.Parameters.Add(new SqlParameter("Changeset", versionFileChangeset.Changeset));
                    cmd.Parameters.Add(new SqlParameter("ChangedUser", versionFileChangeset.ChangedUser));
                    cmd.Parameters.Add(new SqlParameter("ChangedDate", versionFileChangeset.ChangedDate));
                    cmd.Parameters.Add(new SqlParameter("Comment", versionFileChangeset.Comment));
                    cmd.Parameters.Add(new SqlParameter("FileDataId", versionFileChangeset.FileDataId));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool UpdateVersionFileChangesetById(DbContext context, Data.VersionFileChangeset versionFileChangeset)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "update VersionFileChangeset set Changeset = @Changeset ,ChangedUser = @ChangedUser ,ChangedDate = @ChangedDate ,Comment = @Comment ,FileDataId = @FileDataId where Id=@Id)";
                    cmd.Parameters.Add(new SqlParameter("Id", versionFileChangeset.Id));
                    cmd.Parameters.Add(new SqlParameter("Changeset", versionFileChangeset.Changeset));
                    cmd.Parameters.Add(new SqlParameter("ChangedUser", versionFileChangeset.ChangedUser));
                    cmd.Parameters.Add(new SqlParameter("ChangedDate", versionFileChangeset.ChangedDate));
                    cmd.Parameters.Add(new SqlParameter("Comment", versionFileChangeset.Comment));
                    cmd.Parameters.Add(new SqlParameter("FileDataId", versionFileChangeset.FileDataId));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool DeleteVersionFileChangesetById(DbContext context, Guid Id)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "delete from VersionFileChangeset where Id=@Id ";
                    cmd.Parameters.Add(new SqlParameter("Id", Id));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public Data.VersionFileChangeset GetVersionFileChangesetById(DbContext context, Guid Id)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select Id, Changeset, ChangedUser, ChangedDate, Comment, FileDataId from VersionFileChangeset where Id=@Id ";
                        cmd.Parameters.Add(new SqlParameter("Id", Id));

                         DataSet oDataSet = new DataSet();
                         sqlDataAdapter.Fill(oDataSet);

                         Data.VersionFileChangeset versionFileChangeset = new Data.VersionFileChangeset();
                         if (oDataSet.Tables[0].Rows.Count > 0)
                         {
                             versionFileChangeset.Id = new Guid(oDataSet.Tables[0].Rows[0]["Id"].ToString());
                             versionFileChangeset.ChangedDate = Convert.ToDateTime(oDataSet.Tables[0].Rows[0]["ChangedDate"].ToString());
                             versionFileChangeset.ChangedUser = oDataSet.Tables[0].Rows[0]["ChangedUser"].ToString();
                             versionFileChangeset.Changeset = Convert.ToDouble(oDataSet.Tables[0].Rows[0]["Changeset"].ToString());
                             versionFileChangeset.Comment = oDataSet.Tables[0].Rows[0]["Changeset"].ToString();
                             versionFileChangeset.FileDataId = new Guid(oDataSet.Tables[0].Rows[0]["FileDataId"].ToString());
                             return versionFileChangeset;
                         }
                         else
                         {
                             return null;
                         }
                    }
                   
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public PagedList<Data.VersionFileChangeset> GetVersionFileChangesetList(DbContext context, int start, int limit)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from (select *, ROW_NUMBER() OVER(ORDER BY Id) as rownum from [VersionFileChangeset]) a where a.rownum >= @paramStart and a.rownum <= @paramEnd";
                        cmd.Parameters.Add(new SqlParameter("paramStart", start));
                        cmd.Parameters.Add(new SqlParameter("paramEnd", start + limit));
                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        List<Data.VersionFileChangeset> versionFileChangesetList = null;
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            versionFileChangesetList = new List<Data.VersionFileChangeset>();
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                Data.VersionFileChangeset versionFileChangeset = new Data.VersionFileChangeset();
                                versionFileChangeset.Id = new Guid(dr["Id"].ToString());
                             versionFileChangeset.ChangedDate = Convert.ToDateTime(dr["ChangedDate"].ToString());
                             versionFileChangeset.ChangedUser = dr["ChangedUser"].ToString();
                             versionFileChangeset.Changeset = Convert.ToDouble(dr["Changeset"].ToString());
                             versionFileChangeset.Comment =dr["Changeset"].ToString();
                             versionFileChangeset.FileDataId = new Guid(dr["FileDataId"].ToString());
                                versionFileChangesetList.Add(versionFileChangeset);
                            }
                            return new PagedList<Data.VersionFileChangeset>(versionFileChangesetList, start, limit, versionFileChangesetList.Count);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
